CREATE TABLE [dbo].[Name_Salutation]
(
[ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_Salutation_ID] DEFAULT (''),
[SALUTATION_TYPE] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_Salutation_SALUTATION_TYPE] DEFAULT (''),
[SALUTATION_TEXT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_Salutation_SALUTATION_TEXT] DEFAULT (''),
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO

CREATE TRIGGER asi_Name_Salutation_Insert_Update_Delete ON Name_Salutation
AFTER UPDATE, INSERT, DELETE
AS
    DECLARE @UpdateType VARCHAR(1)
    DECLARE @InsertedFlag BIT
    DECLARE @DeletedFlag BIT

    IF (TRIGGER_NESTLEVEL( object_ID('asi_Name_FR_Insert_Update_Delete')) > 0)
        RETURN;

    SET @DeletedFlag = 0;
    SET @InsertedFlag = 0;
    
    IF EXISTS (SELECT 1 FROM INSERTED)
        SET @InsertedFlag=1;
    
    IF EXISTS (SELECT 1 FROM DELETED)
        SET @DeletedFlag=1;
    
    SET @UpdateType='X'
    -- check what we are doing
    IF @InsertedFlag=1 AND @DeletedFlag=0
        SET @UpdateType = 'I'; -- INSERTING

    IF @InsertedFlag=1 AND @DeletedFlag=1
        SET @UpdateType = 'U'; -- UPDATING

    IF @InsertedFlag=0 AND @DeletedFlag=1
        SET @UpdateType = 'D' -- DELETING
    
    IF @InsertedFlag=0 AND @DeletedFlag=0
        RETURN; -- DO NOTHING
    
    SET NOCOUNT ON
    
    -- If inserting or updating
    IF @UpdateType='I' OR @UpdateType='U'
    BEGIN
        -- Update the Name_FR table where the row already exists
        UPDATE [dbo].[Name_FR]
            SET [Name_FR].LIST_AS=i.[SALUTATION_TEXT]
            FROM [INSERTED] i
            WHERE i.[SALUTATION_TYPE]='LIST_AS'
              AND [Name_FR].[ID]=i.[ID] AND [Name_FR].[LIST_AS]<>i.[SALUTATION_TEXT];

        -- insert into Name_FR where the row doesn't already exist
        INSERT INTO [dbo].[Name_FR]
            ( ID ,
              SOLICITOR_ID ,
              DO_NOT_PHONE ,
              DO_NOT_EMAIL ,
              LIST_AS ,
              MATCH_DESCRIP ,
              MATCH_PCT ,
              MIN_EMPL_CONTRIB ,
              MAX_EMPL_CONTRIB ,
              VALID_FROM ,
              VALID_THRU ,
              PREFERRED_DIST_CODE ,
              Receipt_Interval ,
              Last_Receipt_Printed_Date ,
              DO_NOT_SELL ,
              DO_NOT_SOLICIT
            )
        SELECT  i.[ID] , -- ID - varchar(10)
          '' , -- SOLICITOR_ID - varchar(10)
          0 , -- DO_NOT_PHONE - bit
          0 , -- DO_NOT_EMAIL - bit
          i.[SALUTATION_TEXT] , -- LIST_AS - varchar(255)
          '' , -- MATCH_DESCRIP - varchar(255)
          0 , -- MATCH_PCT - tinyint
          0 , -- MIN_EMPL_CONTRIB - money
          0 , -- MAX_EMPL_CONTRIB - numeric
          NULL , -- VALID_FROM - datetime
          NULL , -- VALID_THRU - datetime
          '' , -- PREFERRED_DIST_CODE - varchar(15)
          0 , -- Receipt_Interval - tinyint
          NULL , -- Last_Receipt_Printed_Date - datetime
          0 , -- DO_NOT_SELL - bit
          0  -- DO_NOT_SOLICIT - bit
        FROM [INSERTED] i
            WHERE i.[SALUTATION_TYPE]='LIST_AS'
            AND NOT EXISTS (SELECT 1 FROM [dbo].[Name_FR] WHERE [Name_FR].[ID]=i.[ID]);
                    

    END;
    ELSE IF @UpdateType='D'
    BEGIN    
        -- the row was deleted from Name_Salutation, so clear the value from Name_FR
        -- if it already exists
        UPDATE [Name_FR]
            SET [Name_FR].[LIST_AS]=''
            FROM [DELETED] d
            WHERE d.[SALUTATION_TYPE]='LIST_AS'
              AND [Name_FR].[ID]=d.[ID] AND [Name_FR].[LIST_AS]<>'';
    END;


GO
CREATE NONCLUSTERED INDEX [iName_SalutationID] ON [dbo].[Name_Salutation] ([ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_SalutationSALUTATION_TYP] ON [dbo].[Name_Salutation] ([SALUTATION_TYPE]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[Name_Salutation] TO [IMIS]
GRANT SELECT ON  [dbo].[Name_Salutation] TO [IMIS]
GRANT INSERT ON  [dbo].[Name_Salutation] TO [IMIS]
GRANT DELETE ON  [dbo].[Name_Salutation] TO [IMIS]
GRANT UPDATE ON  [dbo].[Name_Salutation] TO [IMIS]
GO